import time
import pymysql


def get_conn():     #建立与数据库连接和游标，这里封装成函数，方便后续的反复调用
    conn = pymysql.connect(
        host="localhost",
        user="root",
        password="",
        db="weiyi",
        charset="utf8",
        port=3306,
    )
    cursor = conn.cursor()
    return conn, cursor

def close_conn(conn, cursor):       #关闭游标和与数据库的连接，这里也封装成函数，方便后续的反复调用
    if cursor:
        cursor.close()
    if conn:
        conn.close()

def query(sql, *args):      #将上述的两个函数再次封装，后续需要向数据库查询数据时，只需要调用整个函数即可
    conn, cursor= get_conn()
    cursor.execute(sql, args)
    res = cursor.fetchall()
    close_conn(conn, cursor)
    return res

def get_time():     #获取时间
    time_str = time.strftime("%Y{}%m{}%d{} %X")
    return time_str.format("年","月","日")

def get_l1_data():      #获取分数前十的医院表格的数据
    sql = "select hospital,TRUNCATE(avg(star),2) as avg_star from doctor group by hospital having count(hospital)>5 ORDER BY avg_star DESC LIMIT 10"
    res = query(sql)
    return res

def get_l2_data():      #获取医生职称占比表格的数据
    sql = "SELECT job,COUNT(job) AS co FROM doctor GROUP BY job"
    res = query(sql)
    return res

def get_r2_data():      #获取问诊人数前十的科室表格的数据
    sql = "SELECT subject,SUM(number) FROM doctor GROUP BY subject HAVING COUNT(subject)>5 ORDER BY SUM(number) DESC LIMIT 10"
    res = query(sql)
    return res

def get_m1_data():      #获取图片诊断价格盒须图表格的数据
    sql = "SELECT subject,image FROM doctor WHERE subject IN (SELECT x.* FROM(SELECT subject FROM doctor GROUP BY subject HAVING COUNT(subject)>5 ORDER BY SUM(number) DESC) as x) HAVING image != 'None';"
    res = query(sql)
    return res

def get_m2_data():      #获取视频诊断价格盒须图表格的数据
    sql = "SELECT subject,video FROM doctor WHERE subject IN (SELECT x.* FROM(SELECT subject FROM doctor GROUP BY subject HAVING COUNT(subject)>5 ORDER BY SUM(number) DESC) as x) HAVING video != 'None';"
    res = query(sql)
    return res

def get_r1_data():      #获取问诊人数前十的医院表格的数据
    sql = "SELECT hospital,SUM(number) AS sum_num FROM doctor GROUP BY hospital ORDER BY sum_num DESC LIMIT 10;"
    res = query(sql)
    return res

def get_m3_data():      #获取医生擅长词云表格的数据
    sql = "SELECT skill FROM doctor;"
    res = query(sql)
    return res

if __name__ == '__main__':
    pass